
"""
    1.打开表单  load_workbook
    2.读取镖旗
    3.读取所有数据
    4.指定单元格写入数据
"""



from openpyxl import load_workbook
class ExcelHandler():
    """操作excel"""
    def __init__(self,file):
        self.file = file

    def open_sheet(self,name):
        """打开表单"""
        wb = load_workbook(self.file)
        sheet = wb[name]
        return sheet

    def header(self,name):
        """获取表单的表头"""
        sheet =self.open_sheet(name)
        headers =[]
        for i in sheet[1]:
            headers.append(i.value)
        return headers

    def read(self,name):
        """读取所有数据"""
        sheet = self.open_sheet(name)
        rows = list(sheet.rows)[1:]

        data = []
        for row in rows:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
            data.append(row_data)
        return data

    def read1(self,name):
        """读取所有数据"""
        sheet = self.open_sheet(name)
        rows = list(sheet.rows)[1:]

        headers = []
        for i in sheet[1]:
            headers.append(i.value)

        data = []
        for row in rows:
            row_data = {}
            index = 0
            for cell in row:
                row_data[headers[index]] = cell.value
                index +=1
            data.append(row_data)
        return data

    @staticmethod
    def write(file,name,row,column,data):
        """写入数据"""
        wb = load_workbook(file)
        sheet = wb[name]
        #修改单元格
        sheet.cell(row,column).value = data
        # 保存
        wb.save(file)
        # 关闭
        wb.close()





if __name__ =='__main__':
    excel = ExcelHandler(r'G:\cases.xlsx')
    sheet = excel.open_sheet('Sheet1')
    print(sheet)

    header = excel.header('Sheet1')
    print(header)

    read = excel.read('Sheet1')
    print(read)

    excel.write(r'G:\cases.xlsx','Sheet1',3,1,'new_value')
